<?php
ini_set('memory_limit', '1G'); // 文件越大需要设置越大
set_time_limit(30);
session_start();
header('Content-type:text/html;charset=utf8;');
if(strtok(PHP_VERSION, '.') < 7){
    exit('需要php7+版本');
}

// +---------------------------
// | 分析mysql慢查询日志
// +---------------------------
$ignoreIdArr = []; // 排除的记录id
$timeFormat = 'm-d'; // 时间分组格式
$dataId = 1; // 记录id。只要文件内容顺序不变，id就是唯一
$cache = []; // 缓存目录，记录每个记录的执行时间，文件的偏移坐标
$idx = 0;
$hasLib = false; // 是否存在日志目录
// 条件默认值
$cond = [];

// 输入条件
if(!empty($_GET['start_time'])){
    $cond['start_time'] = strtotime($_GET['start_time']);
}
if(!empty($_GET['end_time'])){
    $cond['end_time'] = strtotime($_GET['end_time']);
}
if(!empty($_SESSION['ignoreId']) && !isset($_GET['ignoreId'])){
    $_GET['ignoreId'] = $_SESSION['ignoreId'];
}
if(isset($_GET['ignoreId'])){
    $cond['ignoreId'] = $_GET['ignoreId'];
    $_SESSION['ignoreId'] = $cond['ignoreId'];
    $ignoreIdArr = explode(' ', $cond['ignoreId']);
}
// var_dump($_SESSION);die;

// 读取缓存
$cacheFile = './log/mysql/cache';
if(is_file($cacheFile) && !empty($cond)){
    $cache = json_decode(file_get_contents($cacheFile), true);
    foreach ($cache as $k => $v) {
        if($v['time'] < $cond['start_time']){
            continue;
        }
        $idx = $v['idx'];
        $dataId = $k;
        break;
    }
    // echo '<pre>';
    // var_dump(count($cache), $idx);die;
}

// 初始化结果
$dir = dirname($_GET['file_name']);
$pv = 0; // 总条数
$list = array(); // 详细数据
$timeCount = array(); // 日期分组汇总


$str = '';
$i = 0;
// 日志路径
// $file = "D:\phpstudy_pro\WWW\pc\dkw\\test.log";
$fileName = $_GET['file_name'];
$file = sysPath($fileName); // ./log/https-gamebox.api.gzzy128.com-2025-07-24.log
if(!is_file($file)){
    exit("file empty: '{$file}'");
}
$fileSize = round(filesize($file) / 1024 / 1024, 3); // MB
// 分批处理
$handle = fopen($file, 'rb');
if (!$handle) {
    exit("Cannot open file: {$file}");
}
$idx > 0 && fseek($handle, $idx); // 指定读取的位置
while (($line = fgets($handle)) !== false) {
    // 匹配到算一条记录
    if(preg_match('/\# User@.*?@\s*?\[([\d.]+).*?/i', $line, $m)){
        $id = $dataId++;
        $cache[$id]['idx'] = ftell($handle) - strlen($line); // 记录当前行首的字符偏移位置
        $line .= "[id={$id}]";

        // 排除id
        if(in_array($id, $ignoreIdArr)){
            continue;
        }
    }

    $str .= $line;
    $i++;
    if ($i >= 10000) {
        if(strpos($line, '# User@Host:') !== 0){
            // 上部分未结束，继续获取
            continue;
        }
        $str = rtrim($str, $line);
        // var_dump($str);die;
        calculate($str);
        $str = $line; // 把当前这行加上
        $i = 0;
        // break;
    }
}
// 尾部不足 1 万行
if ($str) {
    calculate($str);
}
fclose($handle);
if(empty($cond)){
    file_put_contents($cacheFile, json_encode($cache));
}

// $hash = 'abc';
// $fp = fopen("{$dir}/cache", 'w');
// fwrite($fp, "{$hash}[");
// $first = true;
// foreach ($list as $row) {
//     if (!$first) fwrite($fp, ',');
//     fwrite($fp, json_encode($row, JSON_UNESCAPED_UNICODE));
//     $first = false;
// }
// fwrite($fp, ']');
// fclose($fp);


// 执行时间排序
$listQuery = $list;
$queryLen = 50;
descArr($listQuery, 'query');
$listQuery = array_slice($listQuery, 0, $queryLen);
// 查询效率
$listRowsRate = $list;
$rowsRateLen = 50;
descArr($listRowsRate, 'rows_rate');
$listRowsRate = array_slice($listRowsRate, 0, $rowsRateLen);
// 正序
$listLen = 20;
$list = array_slice($list, 0, $listLen);

// echo '<pre>';
// var_dump($pv, $listQuery, $listRowsRate);
// exit('_end');

function calculate($str){
    $cond = $GLOBALS['cond'];
    $l1 = "\# User@.*?@\s*?\[([\d.]+).*?\R";
    $l2 = "\[id=(\d+)\]\# Query_time:.*?([\d.]+).*?Lock_time:.*?([\d.]+).*?Rows_sent:.*?([\d.]+).*?Rows_examined:.*?([\d.]+).*?\R";
    $l3 = "(?:use\s+([^\s;]+);\R)?";
    $l4 = "SET timestamp=(\d+);\R";
    $l5 = "([^;]+;)";
    $preg = "/{$l1}{$l2}{$l3}{$l4}{$l5}/mi";
    // exit($preg);
    preg_match_all($preg, $str, $matches);
    // echo '<pre>';
    // var_dump($matches);die;
    // 慢查询条数
    $GLOBALS['pv'] += count($matches[1]);

    foreach ($matches[1] as $k => $v) {
        $id = $matches[2][$k];
        $GLOBALS['cache'][$id]['time'] = $matches[8][$k]; // 记录时间
        $GLOBALS['list'][$id] = array(
            'id' => $id,
            'ip' => $v,
            'query' => $matches[3][$k],
            'lock' => $matches[4][$k],
            'rows_sent' => $matches[5][$k],
            'rows_examined' => $matches[6][$k],
            'time' => $matches[8][$k],
            'datetime' => date('Y-m-d H:i:s', $matches[8][$k]),
            'sql' => $matches[9][$k],

            'lock_rate' => round($matches[4][$k] / $matches[3][$k], 3),
            'rows_rate' => $matches[5][$k] > 0 ? round($matches[6][$k] / $matches[5][$k], 3) : 0,
        );

        // 次数
        $date = datetimeFormat($matches[3][$k]);
        $GLOBALS['timeCount'][$date]['pv'] += 1;
    }
}
// 处理日志的时间格式
function datetimeFormat($time){
    return date($GLOBALS['timeFormat'], $time);
}
// 多维数组，指定字段倒序
function descArr(&$arr, $sortKey){
    uasort($arr, function ($a, $b) use ($sortKey) {
        $sortDesc = true;
        $c = $a[$sortKey] - $b[$sortKey];
        $c != 0 && ($c = $c > 0 ? 1 : -1);
        return $sortDesc ? -$c : $c; // 对比不能有小数
    });
}
// 转码路径成win格式
function sysPath($utf8Path){
    return strtoupper(substr(PHP_OS, 0, 3)) === 'WIN'
           ? iconv('UTF-8', 'GBK//IGNORE', $utf8Path)
           : $utf8Path;
}


// 设置html缓存
$seconds = 3600;
header("Cache-Control: public, max-age=$seconds");
header("Expires: " . gmdate('D, d M Y H:i:s', time() + $seconds) . " GMT");
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />

<title>mysql慢查询日志分析</title>
<style type="text/css">
    a{
        color: #0e72cc;
        text-decoration: underline #0e72cc;
    }
    p{
        margin: 10px 0;
    }
    .form-item{
        display: inline-block;
        padding: 5px;
        border: 1px solid #ccc;
    }
    .form-item input{
        padding: 5px;
    }
    .form-btn{
        display: inline-block;
        margin-top: 10px;
        padding: 6px 18px;
        border: none;
        border-radius: 5px;
        background: #1abc9c;
        color: #fff;
    }
    .title{
        text-align: center;
    }
    .bold{
        font-size: 18px;
        font-weight: bold;
    }
    .tb{
        border-top: 2px solid #000;
        border-collapse: collapse;
        word-break: break-word;
        max-width: max(80%, 1500px);
    }
    .tb th,
    .tb td{
        border: 1px solid #999;
        padding: 5px 8px;
        color: #555;
    }

    .tb .sql{
        padding: 10px 8px 20px;
        border-bottom: 2px solid #000;
        color: #000;
    }
    .js-toggle{
        font-size: 14px;
        font-weight: normal;
    }
    
</style>

</head>
<body>
<div class="main">
    <p>说明：<span class="bold">锁效率</span>：要求小于0.1 | <span class="bold">扫描效率</span>：要求小于100</p>
    <hr>

    <p>文件路径：<? echo $fileName . " (大小：{$fileSize}MB)";?></p>
    <!--搜索-->
    <form action="<? echo '//', $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];?>">
        <input type="hidden" name="file_name" value="<? echo $_GET['file_name'];?>">
        <div class="form-item">
            排除ID：
            <textarea name="ignoreId"
                      placeholder="多个用空格分开"
                      title="多个用空格分开"
                      style="width: 800px;height: 100px;"
            ><? echo $_GET['ignoreId'];?></textarea>
        </div>
        <br>
        <div class="form-item">
            日期：
            <input type="text" name="start_time" value="<? echo $cond['start_time'] ? date('Y-m-d H:i:s', $cond['start_time']) : '';?>"
                   placeholder=""
                   title="可用格式【年-月-日 时:分:秒】【年-月-日】"
                   style="width: 150px;"
                   autocomplete="off"> - 
            <input type="text" name="end_time" value="<? echo $cond['end_time'] ? date('Y-m-d H:i:s', $cond['end_time']) : '';?>"
                   placeholder=""
                   title="可用格式【年-月-日 时:分:秒】【年-月-日】"
                   style="width: 150px;"
                   autocomplete="off">
            <a class="js-query-time" data-val-s="<? echo date('Y-m-01');?>" href="javascript:">本月</a> | 
            <a class="js-query-time" data-val-s="<? echo date('Y-m-d', strtotime('-30day'));?>" href="javascript:">近一月</a>
        </div>
        <div>
            <button class="form-btn" type="submit">查询</button>
        </div>
    </form>
    <hr>
    <p><? echo '记录条数：',number_format($pv);?></p>
    <hr>
</div>

<div class="main">
    <h3>日期顺序(<? echo $listLen;?>条) <a class="js-toggle" href="javascript:">收起/展开</a></h3>
    <table class="tb">
        <?
            $j = 1;
            foreach($list as $k => $v){
                echo '<tr>'
                    ,'<td>No:',$j++,'</td>'
                    ,'<td>ID:',$v['id'],'</td>'
                    ,'<td>时长(秒):',$v['query'],'</td>'
                    ,'<td>日期:',$v['datetime'],'</td>'
                    ,'<td>IP:',$v['ip'],'</td>'
                    ,'<td>锁时长(秒):',$v['lock'],'</td>'
                    ,'<td>锁效率:',$v['lock_rate'] ?? 0,'</td>'
                    ,'<td>返回数&扫描数: ',$v['rows_sent'],' / ',number_format($v['rows_examined']),'</td>'
                    ,'<td>扫描效率:',$v['rows_rate'] ?? 0,'</td>'
                    ,'</tr>';
                echo '<tr>'
                    ,'<td class="sql" colspan="10">',nl2br($v['sql']),'</td>'
                    ,'</tr>';
            }
        ?>
    </table>
</div>

<div class="main">
    <h3>查询时长倒序(<? echo $queryLen;?>条) <a class="js-toggle" href="javascript:">收起/展开</a></h3>
    <table class="tb">
        <?
            $j = 1;
            foreach($listQuery as $k => $v){
                echo '<tr>'
                    ,'<td>No:',$j++,'</td>'
                    ,'<td>ID:',$v['id'],'</td>'
                    ,'<td>时长(秒):',$v['query'],'</td>'
                    ,'<td>日期:',$v['datetime'],'</td>'
                    ,'<td>IP:',$v['ip'],'</td>'
                    ,'<td>锁时长(秒):',$v['lock'],'</td>'
                    ,'<td>锁效率:',$v['lock_rate'] ?? 0,'</td>'
                    ,'<td>返回数&扫描数: ',$v['rows_sent'],' / ',number_format($v['rows_examined']),'</td>'
                    ,'<td>扫描效率:',$v['rows_rate'] ?? 0,'</td>'
                    ,'</tr>';
                echo '<tr>'
                    ,'<td class="sql" colspan="10">',nl2br($v['sql']),'</td>'
                    ,'</tr>';
            }
        ?>
    </table>
</div>

<div class="main">
    <h3>扫描效率倒序(<? echo $rowsRateLen;?>条) <a class="js-toggle" href="javascript:">收起/展开</a></h3>
    <table class="tb">
        <?
            $j = 1;
            foreach($listRowsRate as $k => $v){
                echo '<tr>'
                    ,'<td>No:',$j++,'</td>'
                    ,'<td>ID:',$v['id'],'</td>'
                    ,'<td>时长(秒):',$v['query'],'</td>'
                    ,'<td>日期:',$v['datetime'],'</td>'
                    ,'<td>IP:',$v['ip'],'</td>'
                    ,'<td>锁时长(秒):',$v['lock'],'</td>'
                    ,'<td>锁效率:',$v['lock_rate'] ?? 0,'</td>'
                    ,'<td>返回数&扫描数: ',$v['rows_sent'],' / ',number_format($v['rows_examined']),'</td>'
                    ,'<td>扫描效率:',$v['rows_rate'] ?? 0,'</td>'
                    ,'</tr>';
                echo '<tr>'
                    ,'<td class="sql" colspan="10">',nl2br($v['sql']),'<br>-</td>'
                    ,'</tr>';
            }
        ?>
    </table>
</div>

<script src="./static/jquery-3.6.1.min.js" type="text/javascript"></script>
<script type="text/javascript">
    // 收起&展开
    $('.js-toggle').on('click', function(){
        $(this).parent().next('table').toggle();
    });

    $('.js-query-time').on('click', function(){
        let s = $(this).data('val-s');
        $('.form-item input[name=start_time]').val(s);
        $('.form-item').parent('form').submit();
    });
</script>
</body>
</html>
